﻿using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System.Data;

namespace Wechat_PublicNumber.Common
{
    [Injection(DIPattern.Singleton)]
    public class ExcelHelper
    {
        /// <summary>
        /// 生成datatable到excel
        /// </summary>
        /// <param name="data"></param>
        /// <param name="path"></param>
        /// <param name="tableHeaderCellStyle"></param>
        /// <param name="tableRowCellStyle"></param>
        public void CreateExcel(DataTable data, string path,ICellStyle tableHeaderCellStyle = null, ICellStyle tableRowCellStyle = null)
        {
            Task.Factory.StartNew(() =>
            {
                //创建工作簿
                var workbook = new HSSFWorkbook();
                //生成文件基本信息
                Generatesummaryinformation(workbook);
                //创建工作表
                var sheet = workbook.CreateSheet("sheet1");

                //创建标题行
                if (data != null && data.Columns.Count > 0)
                {
                    IRow row = sheet.CreateRow(0);
                    for (int i = 0; i < data.Columns.Count; i++)
                    {
                        var cell = row.CreateCell(i);
                        cell.SetCellValue(data.Columns[i].ColumnName);
                        if (tableHeaderCellStyle != null)
                            cell.CellStyle = tableHeaderCellStyle;

                    }
                }
                //创建数据行
                if (data != null && data.Rows.Count > 0)
                {
                    for (int rowindex = 1; rowindex <= data.Rows.Count; rowindex++)
                    {
                        IRow row = sheet.CreateRow(rowindex);
                        for (int colindex = 0; colindex < data.Columns.Count; colindex++)
                        {
                            var cell = row.CreateCell(colindex);
                            var cellvalue = data.Rows[rowindex - 1][colindex];
                            switch (data.Columns[colindex].DataType.Name)
                            {
                                case "byte":
                                case "int16":
                                case "int32":
                                case "int64":
                                case "decimal":
                                case "single":
                                case "double":
                                    double doubleval = 0;
                                    if (cellvalue != null && !cellvalue.Equals(DBNull.Value))
                                    {
                                        double.TryParse(cellvalue.ToString(), out doubleval);
                                        cell.SetCellValue(doubleval);
                                    }
                                    break;
                                case "datetime":
                                    DateTime dtval = DateTime.MinValue;
                                    if (cellvalue != null && !cellvalue.Equals(DBNull.Value))
                                    {
                                        DateTime.TryParse(cellvalue.ToString(), out dtval);
                                        if (dtval != DateTime.MinValue)
                                        {
                                            cell.SetCellValue(dtval);
                                        }
                                    }
                                    break;
                                default:
                                    if (cellvalue != null && !cellvalue.Equals(DBNull.Value))
                                    {
                                        cell.SetCellValue(cellvalue.ToString());
                                    }
                                    break;
                            }
                            if (tableRowCellStyle != null)
                                cell.CellStyle = tableRowCellStyle;
                        }
                    }
                }

                using (FileStream fs = File.Create(path)) //打开一个xls文件，如果没有则自行创建，如果存在则在创建时不要打开该文件！
                {
                    workbook.Write(fs);    //向打开的这个xls文件中写入mysheet表并保存。
                }
                workbook.Close();
            });
        }

        /// <summary>
        /// 创建文档的基本信息(右击文件属性可看到的)
        /// </summary>
        /// <param name="workbook"></param>
        public void Generatesummaryinformation(HSSFWorkbook workbook)
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "company";

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "subject";//主题
            si.Author = "author";//作者

            workbook.DocumentSummaryInformation = dsi;
            workbook.SummaryInformation = si;
        }
    }
}
